﻿-- 培训记录报表
CREATE procedure [dbo].[Report_Pxjl]
@qybmm varchar(100) = null,
@begindate datetime = null,
@enddate datetime = null
as
with peixun
as
(
select jsdm,sfzh,xm,qybmm,kqsj,jczt,jllx from Kqjl_Info 
where jllx=1 and (@qybmm is null or qybmm=@qybmm) and (@begindate is null or kqsj>=@begindate)
and (@enddate is null or kqsj<=@enddate)
)

select aa.jsdm,aa.sfzh,aa.xm,aa.qybmm,aa.kqrq,convert(char(8),kqsjin,8) sjin,convert(char(8),kqsjout,8) sjout,DATEDIFF(ss,kqsjin,kqsjout) sjdiff
from
(
select jsdm,sfzh,xm,qybmm,convert(char(10),kqsj,120) kqrq,max(kqsj) kqsjout
from peixun 
where jczt=0
group by jsdm,sfzh,xm,qybmm,convert(char(10),kqsj,120)
) aa
left join
(
select jsdm,sfzh,xm,qybmm,convert(char(10),kqsj,120) kqrq,min(kqsj) kqsjin
from peixun 
where jczt=1
group by jsdm,sfzh,xm,qybmm,convert(char(10),kqsj,120)
)bb on (aa.jsdm=bb.jsdm and aa.sfzh=bb.sfzh and aa.xm=bb.xm and aa.qybmm=bb.qybmm and aa.kqrq=bb.kqrq)